package powell.nflpredictionproject.database;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class PopulateDatabase
{

    private int count2012 = 0;
    private int count2013 = 0;
    
     public void Populate() throws FileNotFoundException, SQLException
    {
        File inFile = new File("Stats.txt");
        Scanner readFile = new Scanner(inFile);
        String[] line;
        File inFile2013 = new File("2013Stats.txt");
        Scanner readFile2013 = new Scanner(inFile2013);
        String[] line2013;
        Connection conn = DriverManager.getConnection("jdbc:derby:NFLStatsDatabase");
        Statement stmt = conn.createStatement();



        while (readFile.hasNextLine())
        {

            line = readFile.nextLine().split(",");

            for (int index = 0; index < line.length; index++)
            {
                System.out.println(line[index]);
            }
            
            if (count2012 == 1)
            {
                stmt.execute("Insert into STATS2012 Values("
                        + "'" + line[1] + "', "
                        + "" + line[2] + ", "
                        + "" + line[3] + ", "
                        + "'" + line[4] + "', "
                        + "" + line[5] + ", "
                        + "" + line[6] + ", "
                        + "" + line[7] + ", "
                        + "" + line[8] + ", "
                        + "" + line[9] + ", "
                        + "" + line[10] + ", "
                        + "" + line[11] + ", "
                        + "" + line[12] + ", "
                        + "" + line[13] + ", "
                        + "" + line[14] + ", "
                        + "'" + line[15] + "', "
                        + "" + line[16] + "" + ")");
            }
            count2012 = 1;
        }
        
        while (readFile2013.hasNextLine())
        {

            line2013 = readFile2013.nextLine().split(",");

            for (int index = 0; index < line2013.length; index++)
            {
                System.out.println(line2013[index]);
            }
            
            if (count2013 == 1)
            {
                stmt.execute("Insert into STATS2013 Values("
                        + "'" + line2013[1] + "', "
                        + "" + line2013[2] + ", "
                        + "" + line2013[3] + ", "
                        + "'" + line2013[4] + "', "
                        + "" + line2013[5] + ", "
                        + "" + line2013[6] + ", "
                        + "" + line2013[7] + ", "
                        + "" + line2013[8] + ", "
                        + "" + line2013[9] + ", "
                        + "" + line2013[10] + ", "
                        + "" + line2013[11] + ", "
                        + "" + line2013[12] + ", "
                        + "" + line2013[13] + ", "
                        + "" + line2013[14] + ", "
                        + "'" + line2013[15] + "', "
                        + "" + line2013[16] + "" + ")");
            }
            count2013 = 1;
        }
        conn.close();
    }
    
    public static String removeCharAt(String s, int pos) {
      return s.substring(0, pos) + s.substring(pos + 1);
   }
}
